#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : day_report_business_service.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-08-12 15:18 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

from report_system.utils import excel_util
from report_system.utils import mail_util
from report_system.utils import mailbox_util
from report_system.utils.num_util import divi
from report_system.utils.enum_util import DAY_REPORT_BUSINESS_COLOR
from report_system.utils.channel_util import TUJIA, ZHENGUO, AIRBNB
from report_system.report_specify.business_specify import *
from report_system.report_specify.service_specify import im_city_day, city_refund_rate_month
from report_system.utils.development_util import debug

if debug:
    receivers = 'dt@iyoujia.com;wujiming@iyoujia.com'
    city_mails = mailbox_util.mails_test
else:
    receivers = 'yitangf@sweetome.com;yangweiwei@iyoujia.com;machao@iyoujia.com;' \
                'wangjing@iyoujia.com;wangjian@iyoujia.com;dt@iyoujia.com;wangchao@iyoujia.com;' \
                'yy@iyoujia.com;ccsj@iyoujia.com;zhuqiang@iyoujia.com;lvweixin@iyoujia.com'
    city_mails = mailbox_util.mails_all


def process(st=None, et=None):
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    sql = """select 
            city.short_name 城市,
            concat(bo.id ,"") 订单编号,
            date(bo.checkin_day) 预定入住日期,
            date(bo.checkout_day)  预定退房日期,
            date(bo.actcheckin_day) 实际入住日期,
            date(bo.actcheckout_day) 实际退房日期,
            l.title 房源名称,
            concat(l.id,"") 房源编号,
            concat(od.house_id,"") 房屋编号,
            oe.tenant_name 房客姓名,
            oe.tenant_mobile 房客电话,
            sc.name 预订平台,
            
            if(bo.cancel_status = 0 ,bo.real_income,bo.cut_part)/100 订单总额,
            case bo.cancel_status 
            when 0 then '未取消'
            when 1 then '房客取消支付超时'
            when 2 then '房客取消未付款'
            when 3 then '房客取消全额退款'
            when 4 then '房客取消部分扣款'
            when 5 then '房客取消全额扣款'
            when 6 then '管家拒绝订单取消'
            when 7 then '管家拒绝全额退款'
            when 8 then '管家拒绝确认超时'
            when 9 then '无房了'
            when 10 then '客服介入'
            else ""
            end 订单状态,
             
            users1.realname 线上销售,
            users2.realname 线下管家,
            if(bo.brush_status,"是","否") 是否刷单,
            date(bo.create_time) 添加时间,
            
            ct.score 评分,
            ct.content 评价
            
            from youjia.orders bo
            left join youjia.lodge l on l.id = bo.lodgeunit_id
            left join youjia.order_detail od on od.order_id = bo.id
            left join youjia.order_extend oe on oe.order_id = bo.id
            left join youjia_common.sales_channel sc on sc.id = bo.order_channel
            left join youjia.user_house_relation uhr1 on uhr1.house_id = od.house_id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users users1 ON uhr1.user_id=users1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = od.house_id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users users2 ON uhr2.user_id=users2.id
            left join youjia.city on city.id = bo.city_id
            LEFT JOIN youjia.comment ct on ct.order_id = bo.id AND ct.comment_type=0
            where bo.city_id <> 376
            AND (bo.pay_state = 1  or (bo.pay_state = 0 and  order_channel=5))
            AND bo.confirm_status = 1 
            and bo.cancel_status = 0
            and date(bo.checkout_day) between '{st}' and '{et}'""".format(st=st,et=et)
    df = pd.read_sql_query(sql, db_util.con_youjia)
    df_dict = {'经营日报-订单': df}
    colors_dict = {'经营日报-订单': DAY_REPORT_BUSINESS_COLOR}
    #
    text = """    Dear All:
                    附件是 订单经营日报！
                    请查收！
                    谢谢！
            如有问题！
            请联系: dt@iyoujia.com
            """
    mail_util.distribute_mail(df_dict=df_dict, file_name='经营日报-订单', text=text, engine='xlsxwriter',
                              # html_sheet_name='经营日报',
                              title="【经营日报-订单】", colors_dict=colors_dict, receiver=receivers, na_rep='-',
                              uuid='city', freeze_panes=(2, 2))

    # excel_util.pd_to_excel({'result_df': result_df}, '00订单', engine='xlsxwriter', merge_cells=True)


if __name__ == '__main__':
    process()
